package com.thermoberg.zhousi.dao.impl;

import com.thermoberg.zhousi.dao.DeviceDao;
import com.thermoberg.zhousi.model.Admin;
import com.thermoberg.zhousi.model.Device;
import com.thermoberg.zhousi.model.HistoryData;
import com.thermoberg.zhousi.model.LastDate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by zhaoyou on 07/08/2017.
 */
@Repository
public class DeviceDaoImpl implements DeviceDao {

    private JdbcTemplate template;

    private SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Autowired
    public void setDataSource(DataSource dataSource) {
        template = new JdbcTemplate(dataSource);
    }


    @Override
    public List<Device> findDevice() {
        String sql = "select * from device";
        return this.template.query(sql, new RowMapper<Device>() {
            @Override
            public Device mapRow(ResultSet rs, int i) throws SQLException {
                Device d = new Device();
                d.setId(rs.getInt("id"));
                d.setTagId(rs.getString("tagId"));
                return d;
            }
        });
    }

    @Override
    public List<HistoryData> findHistoryData(String tagId, String startTime, String endTime) {
        String sql = "select id, tagId, temperature, humidity, recordTime, uploadTime from sensordata where tagId = ? and recordTime >= ? " +
                " and recordTime <= ? order by recordTime desc";

        return this.template.query(sql, new Object[]{tagId, startTime, endTime}, new RowMapper<HistoryData>() {
            @Override
            public HistoryData mapRow(ResultSet rs, int i) throws SQLException {
                HistoryData d = new HistoryData();
                d.setId(rs.getInt("id"));
                d.setTagId(rs.getString("tagId"));
                d.setTemperature(rs.getDouble("temperature"));
                d.setHumidity(rs.getDouble("humidity"));
                d.setRecordTime(rs.getTimestamp("recordTime"));
                d.setUploadTime(rs.getTimestamp("uploadTime"));
                return d;
            }
        });
    }

    @Override
    public List<String> findRepeatData(String tagId, String startTime, String endTime) {
        String sql = "select recordTime from sensordata where tagId = ? and recordTime >= ? and recordTime <= ? group by recordTime having count(*) > 1";

        return this.template.query(sql, new Object[]{tagId, startTime, endTime}, new RowMapper<String>() {
            @Override
            public String mapRow(ResultSet rs, int i) throws SQLException {
                return sf.format(rs.getTimestamp("recordTime"));
            }
        });
    }

    @Override
    public Admin findAdminPassword(String adminCode) {
        String sql="select* from admin_liu where adminCode=?";
        return this.template.queryForObject(sql, new Object[]{adminCode}, new RowMapper<Admin>() {
            @Override
            public Admin mapRow(ResultSet rs, int i) throws SQLException {
                Admin a=new Admin();
                a.setAdminCode(rs.getString("adminCode"));
                a.setAdminId(rs.getInt("adminId"));
                a.setAdminName(rs.getString("adminName"));
                a.setAdminPassword(rs.getString("adminPassword"));
                a.setEmail(rs.getString("email"));
                a.setEnrolldate(rs.getTimestamp("enrolldate"));
                a.setTelephone(rs.getString("telephone"));

                return a;
            }
        });
    }

    @Override
    public  List<LastDate> findLastRecord(){
        String sqll="select " +
                       "s.tagId,temperature,humidity,recordTime " +
                   "from " +
                       "sensordata s,( select tagId,max(recordTime) mr from sensordata group by tagId) m " +
                  " where " +
                       "s.tagId = m.tagId and s.recordTime = m.mr order by recordTime desc ";
        String sql = "select s.tagId,temperature,humidity,recordTime from sensordata s,( select tagId,max(recordTime) mr from sensordata group by tagId) m  where s.tagId=m.tagId and s.recordTime=m.mr";
        return this.template.query(sqll, new RowMapper<LastDate>() {
            @Override
            public LastDate mapRow(ResultSet rs ,int i) throws SQLException{
                   LastDate lastDate = new LastDate();
                   lastDate.setTagId(rs.getString("tagId"));
                   lastDate.setTemperature(rs.getDouble("temperature"));
                   lastDate.setHumidity(rs.getDouble("humidity"));
                   lastDate.setRecordTime(rs.getTimestamp("recordTime"));
                   long time = rs.getTimestamp("recordTime").getTime();
                   SimpleDateFormat fmt=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                   lastDate.setRecordTimeStr(fmt.format(new Date(time)));
                   return lastDate;
            }

        });
    }

    @Override
    public List<HistoryData> findHistoryData() {
        String s = "select a.* from sensordata a join " +
                "(select tagId,max(recordTime) lastTime from sensordata GROUP BY tagId) b " +
                "on a.recordTime=b.lastTime and a.tagId=b.tagId order by recordTime DESC ";
        return this.template.query(s, new RowMapper<HistoryData>() {
            @Override
            public HistoryData mapRow(ResultSet rs, int i) throws SQLException {
                HistoryData data = new HistoryData();
                data.setId(rs.getInt("id"));
                data.setTagId(rs.getString("tagId"));
                data.setHumidity(rs.getDouble("humidity"));
                data.setTemperature(rs.getDouble("temperature"));
                data.setRecordTime(rs.getTimestamp("recordTime"));
                data.setUploadTime(rs.getTimestamp("uploadTime"));
                data.setStartTime(sf.format(rs.getTimestamp("recordTime")));
                return data;


            }
        });
    }




    }